<template><div><h2 id="数据库-查询生成器" tabindex="-1"><a class="header-anchor" href="#数据库-查询生成器"><span>数据库：查询生成器</span></a></h2>
<ul>
<li><a href="#introduction">介绍</a></li>
<li><a href="#running-database-queries">运行数据库查询</a>
<ul>
<li><a href="#chunking-results">分块结果</a></li>
<li><a href="#streaming-results-lazily">延迟流式处理结果</a></li>
<li><a href="#aggregates">聚合</a></li>
</ul>
</li>
<li><a href="#select-statements">Select 语句</a></li>
<li><a href="#raw-expressions">原始表达式</a></li>
<li><a href="#joins">Joins</a></li>
<li><a href="#unions">Unions</a></li>
<li><a href="#basic-where-clauses">基础 Where 语句</a>
<ul>
<li><a href="#where-clauses">条件查询语句</a></li>
<li><a href="#or-where-clauses">Or Where 语句</a></li>
<li><a href="#where-not-clauses">Where Not 语句</a></li>
<li><a href="#json-where-clauses">JSON Where 语句</a></li>
<li><a href="#additional-where-clauses">附加 Where 语句</a></li>
<li><a href="#logical-grouping">逻辑分组</a></li>
</ul>
</li>
<li><a href="#advanced-where-clauses">高级 Where 语句</a>
<ul>
<li><a href="#where-exists-clauses">Where Exists 语句</a></li>
<li><a href="#subquery-where-clauses">子查询 Where 语句</a></li>
<li><a href="#full-text-where-clauses">全文 Where 子句</a></li>
</ul>
</li>
<li><a href="#ordering-grouping-limit-and-offset">排序、分组、限制和偏移量</a>
<ul>
<li><a href="#ordering">排序</a></li>
<li><a href="#grouping">分组</a></li>
<li><a href="#limit-and-offset">Limit（限制） &amp; Offset（偏移量）</a></li>
</ul>
</li>
<li><a href="#conditional-clauses">条件语句</a></li>
<li><a href="#insert-statements">插入语句</a>
<ul>
<li><a href="#upserts">更新插入</a></li>
</ul>
</li>
<li><a href="#update-statements">更新语句</a>
<ul>
<li><a href="#updating-json-columns">更新 JSON 列</a></li>
<li><a href="#increment-and-decrement">自增和自减</a></li>
</ul>
</li>
<li><a href="#delete-statements">删除语句</a></li>
<li><a href="#pessimistic-locking">悲观锁</a></li>
<li><a href="#debugging">调试</a></li>
</ul>
<h2 id="介绍" tabindex="-1"><a class="header-anchor" href="#介绍"><span>介绍</span></a></h2>
<p>Laravel 的数据库查询生成器提供了一种便捷、流畅的接口来创建和运行数据库查询。它可用于执行应用程序中的大多数数据库操作，并与 Laravel 支持的所有数据库系统完美配合使用。</p>
<p>Laravel 查询生成器使用 PDO 参数绑定来保护你的应用程序免受 SQL 注入攻击。无需清理或净化传递给查询生成器的字符串作为查询绑定。</p>
<blockquote>
<p><strong>警告</strong><br>
PDO 不支持绑定列名。因此，你不应该允许用户输入来决定查询引用的列名，包括 「order by」 列名。</p>
</blockquote>
<h2 id="运行数据库查询" tabindex="-1"><a class="header-anchor" href="#运行数据库查询"><span>运行数据库查询</span></a></h2>
<h4 id="从表中检索所有行" tabindex="-1"><a class="header-anchor" href="#从表中检索所有行"><span>从表中检索所有行</span></a></h4>
<p>你可以使用 <code v-pre>DB</code> facade 提供的 <code v-pre>table</code> 方法开始查询。table 方法为指定的表返回一个链式查询构造器实例，允许在查询上链接更多约束，最后使用 <code v-pre>get</code> 方法检索查询结果：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token php language-php"><span class="token delimiter important">&lt;?php</span></span>
<span class="line"></span>
<span class="line"><span class="token keyword">namespace</span> <span class="token package">App<span class="token punctuation">\</span>Http<span class="token punctuation">\</span>Controllers</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token keyword">use</span> <span class="token package">App<span class="token punctuation">\</span>Http<span class="token punctuation">\</span>Controllers<span class="token punctuation">\</span>Controller</span><span class="token punctuation">;</span></span>
<span class="line"><span class="token keyword">use</span> <span class="token package">Illuminate<span class="token punctuation">\</span>Support<span class="token punctuation">\</span>Facades<span class="token punctuation">\</span>DB</span><span class="token punctuation">;</span></span>
<span class="line"><span class="token keyword">use</span> <span class="token package">Illuminate<span class="token punctuation">\</span>View<span class="token punctuation">\</span>View</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token keyword">class</span> <span class="token class-name-definition class-name">UserController</span> <span class="token keyword">extends</span> <span class="token class-name">Controller</span></span>
<span class="line"><span class="token punctuation">{</span></span>
<span class="line">    <span class="token doc-comment comment">/**</span>
<span class="line">     * 展示应用程序所有用户的列表</span>
<span class="line">     */</span></span>
<span class="line">    <span class="token keyword">public</span> <span class="token keyword">function</span> <span class="token function-definition function">index</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">:</span> <span class="token class-name return-type">View</span></span>
<span class="line">    <span class="token punctuation">{</span></span>
<span class="line">        <span class="token variable">$users</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line">        <span class="token keyword">return</span> <span class="token function">view</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'user.index'</span><span class="token punctuation">,</span> <span class="token punctuation">[</span><span class="token string single-quoted-string">'users'</span> <span class="token operator">=></span> <span class="token variable">$users</span><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line">    <span class="token punctuation">}</span></span>
<span class="line"><span class="token punctuation">}</span></span>
<span class="line"></span></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p><code v-pre>get</code> 方法返回包含查询结果的 <code v-pre>Illuminate\Support\Collection</code> 实例，每个结果都是 PHP <code v-pre>stdClass</code> 实例。可以将列作为对象的属性来访问每列的值：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token keyword">use</span> <span class="token package">Illuminate<span class="token punctuation">\</span>Support<span class="token punctuation">\</span>Facades<span class="token punctuation">\</span>DB</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token keyword">foreach</span> <span class="token punctuation">(</span><span class="token variable">$users</span> <span class="token keyword">as</span> <span class="token variable">$user</span><span class="token punctuation">)</span> <span class="token punctuation">{</span></span>
<span class="line">    <span class="token keyword">echo</span> <span class="token variable">$user</span><span class="token operator">-></span><span class="token property">name</span><span class="token punctuation">;</span></span>
<span class="line"><span class="token punctuation">}</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><blockquote>
<p><strong>技巧:</strong><br>
Laravel 集合提供了各种及其强大的方法来映射和裁剪数据。有关 Laravel 集合的更多信息，请查看 <a href="https://learnku.com/docs/laravel/10.x/collections" target="_blank" rel="noopener noreferrer">集合文档</a>.</p>
</blockquote>
<h4 id="从表中检索单行或单列" tabindex="-1"><a class="header-anchor" href="#从表中检索单行或单列"><span>从表中检索单行或单列</span></a></h4>
<p>如果只需要从数据表中检索单行，可以使用 <code v-pre>DB</code> facade 中的 <code v-pre>first</code> 方法。 此方法将返回单个 <code v-pre>stdClass</code> 对象</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$user</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">where</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'name'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'John'</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">first</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token keyword">return</span> <span class="token variable">$user</span><span class="token operator">-></span><span class="token property">email</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>如果不想要整行，可以使用 <code v-pre>value</code> 方法从纪录中提取单个值。此方法将直接返回列的值：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$email</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">where</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'name'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'John'</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">value</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'email'</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div></div></div><p>如果要通过 <code v-pre>id</code> 字段值获取单行数据，可以使用 <code v-pre>find</code> 方法：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$user</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">find</span><span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div></div></div><h4 id="获取某一列的值" tabindex="-1"><a class="header-anchor" href="#获取某一列的值"><span>获取某一列的值</span></a></h4>
<p>如果要获取包含单列值的 <code v-pre>Illuminate\Support\Collection</code> 实例，则可以使用 <code v-pre>pluck</code> 方法。在下面的例子中，我们将获取角色表中标题的集合：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token keyword">use</span> <span class="token package">Illuminate<span class="token punctuation">\</span>Support<span class="token punctuation">\</span>Facades<span class="token punctuation">\</span>DB</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token variable">$titles</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">pluck</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'title'</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token keyword">foreach</span> <span class="token punctuation">(</span><span class="token variable">$titles</span> <span class="token keyword">as</span> <span class="token variable">$title</span><span class="token punctuation">)</span> <span class="token punctuation">{</span></span>
<span class="line">    <span class="token keyword">echo</span> <span class="token variable">$title</span><span class="token punctuation">;</span></span>
<span class="line"><span class="token punctuation">}</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>你可以通过向 <code v-pre>pluck</code> 方法提供第二个参数来指定结果集中要作为键的列：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$titles</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">pluck</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'title'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'name'</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token keyword">foreach</span> <span class="token punctuation">(</span><span class="token variable">$titles</span> <span class="token keyword">as</span> <span class="token variable">$name</span> <span class="token operator">=></span> <span class="token variable">$title</span><span class="token punctuation">)</span> <span class="token punctuation">{</span></span>
<span class="line">    <span class="token keyword">echo</span> <span class="token variable">$title</span><span class="token punctuation">;</span></span>
<span class="line"><span class="token punctuation">}</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h3 id="分块结果" tabindex="-1"><a class="header-anchor" href="#分块结果"><span>分块结果</span></a></h3>
<p>如果需要处理成千上万的数据库记录，请考虑使用 <code v-pre>DB</code> 提供的 <code v-pre>chunk</code> 方法。这个方法一次检索一小块结果，并将每个块反馈到闭包函数中进行处理。例如，让我们以一次 100 条记录的块为单位检索整个 <code v-pre>users</code> 表：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token keyword">use</span> <span class="token package">Illuminate<span class="token punctuation">\</span>Support<span class="token punctuation">\</span>Collection</span><span class="token punctuation">;</span></span>
<span class="line"><span class="token keyword">use</span> <span class="token package">Illuminate<span class="token punctuation">\</span>Support<span class="token punctuation">\</span>Facades<span class="token punctuation">\</span>DB</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">orderBy</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'id'</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">chunk</span><span class="token punctuation">(</span><span class="token number">100</span><span class="token punctuation">,</span> <span class="token keyword">function</span> <span class="token punctuation">(</span><span class="token class-name type-declaration">Collection</span> <span class="token variable">$users</span><span class="token punctuation">)</span> <span class="token punctuation">{</span></span>
<span class="line">    <span class="token keyword">foreach</span> <span class="token punctuation">(</span><span class="token variable">$users</span> <span class="token keyword">as</span> <span class="token variable">$user</span><span class="token punctuation">)</span> <span class="token punctuation">{</span></span>
<span class="line">        <span class="token comment">// ...</span></span>
<span class="line">    <span class="token punctuation">}</span></span>
<span class="line"><span class="token punctuation">}</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>你可以通过从闭包中返回 <code v-pre>false</code> 来停止处理其余的块:</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">orderBy</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'id'</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">chunk</span><span class="token punctuation">(</span><span class="token number">100</span><span class="token punctuation">,</span> <span class="token keyword">function</span> <span class="token punctuation">(</span><span class="token class-name type-declaration">Collection</span> <span class="token variable">$users</span><span class="token punctuation">)</span> <span class="token punctuation">{</span></span>
<span class="line">    <span class="token comment">// 处理分块...</span></span>
<span class="line"></span>
<span class="line">    <span class="token keyword">return</span> <span class="token constant boolean">false</span><span class="token punctuation">;</span></span>
<span class="line"><span class="token punctuation">}</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>如果在对结果进行分块时更新数据库记录，那分块结果可能会以意想不到的方式更改。如果你打算在分块时更新检索到的记录，最好使用 <code v-pre>chunkById</code> 方法。此方法将根据记录的主键自动对结果进行分页:</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">where</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'active'</span><span class="token punctuation">,</span> <span class="token constant boolean">false</span><span class="token punctuation">)</span></span>
<span class="line">    <span class="token operator">-></span><span class="token function">chunkById</span><span class="token punctuation">(</span><span class="token number">100</span><span class="token punctuation">,</span> <span class="token keyword">function</span> <span class="token punctuation">(</span><span class="token class-name type-declaration">Collection</span> <span class="token variable">$users</span><span class="token punctuation">)</span> <span class="token punctuation">{</span></span>
<span class="line">        <span class="token keyword">foreach</span> <span class="token punctuation">(</span><span class="token variable">$users</span> <span class="token keyword">as</span> <span class="token variable">$user</span><span class="token punctuation">)</span> <span class="token punctuation">{</span></span>
<span class="line">            <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">where</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'id'</span><span class="token punctuation">,</span> <span class="token variable">$user</span><span class="token operator">-></span><span class="token property">id</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">update</span><span class="token punctuation">(</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'active'</span> <span class="token operator">=></span> <span class="token constant boolean">true</span><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line">        <span class="token punctuation">}</span></span>
<span class="line">    <span class="token punctuation">}</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><blockquote>
<p><strong>注意</strong><br>
当在更新或删除块回调中的记录时，对主键或外键的任何更改都可能影响块查询。这可能会导致记录未包含在分块结果中。</p>
</blockquote>
<h3 id="lazily-流式传输结果" tabindex="-1"><a class="header-anchor" href="#lazily-流式传输结果"><span>Lazily 流式传输结果</span></a></h3>
<p><code v-pre>lazy</code> 方法的工作方式类似于 <a href="#chunking-results"><code v-pre>chunk</code> 方法</a>，因为它以块的形式执行查询。但是，<code v-pre>lazy()</code> 方法不是将每个块传递给回调，而是返回一个 <a href="https://learnku.com/docs/laravel/10.x/collections#lazy-collections" target="_blank" rel="noopener noreferrer"><code v-pre>LazyCollection</code></a>，它可以让你与结果进行交互单个流：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token keyword">use</span> <span class="token package">Illuminate<span class="token punctuation">\</span>Support<span class="token punctuation">\</span>Facades<span class="token punctuation">\</span>DB</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">orderBy</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'id'</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">lazy</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">each</span><span class="token punctuation">(</span><span class="token keyword">function</span> <span class="token punctuation">(</span><span class="token keyword type-hint">object</span> <span class="token variable">$user</span><span class="token punctuation">)</span> <span class="token punctuation">{</span></span>
<span class="line">    <span class="token comment">// ...</span></span>
<span class="line"><span class="token punctuation">}</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>再一次，如果你打算在迭代它们时更新检索到的记录，最好使用 <code v-pre>lazyById</code> 或 <code v-pre>lazyByIdDesc</code> 方法。 这些方法将根据记录的主键自动对结果进行分页：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">where</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'active'</span><span class="token punctuation">,</span> <span class="token constant boolean">false</span><span class="token punctuation">)</span></span>
<span class="line">    <span class="token operator">-></span><span class="token function">lazyById</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">each</span><span class="token punctuation">(</span><span class="token keyword">function</span> <span class="token punctuation">(</span><span class="token keyword type-hint">object</span> <span class="token variable">$user</span><span class="token punctuation">)</span> <span class="token punctuation">{</span></span>
<span class="line">        <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span></span>
<span class="line">            <span class="token operator">-></span><span class="token function">where</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'id'</span><span class="token punctuation">,</span> <span class="token variable">$user</span><span class="token operator">-></span><span class="token property">id</span><span class="token punctuation">)</span></span>
<span class="line">            <span class="token operator">-></span><span class="token function">update</span><span class="token punctuation">(</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'active'</span> <span class="token operator">=></span> <span class="token constant boolean">true</span><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line">    <span class="token punctuation">}</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><blockquote>
<p><strong>注意</strong><br>
在迭代记录时更新或删除记录时，对主键或外键的任何更改都可能影响块查询。这可能会导致记录不包含在结果中。</p>
</blockquote>
<h3 id="聚合函数" tabindex="-1"><a class="header-anchor" href="#聚合函数"><span>聚合函数</span></a></h3>
<p>查询构建器还提供了多种检索聚合值的方法，例如 <code v-pre>count</code>， <code v-pre>max</code>， <code v-pre>min</code>，<code v-pre>avg</code>和 <code v-pre>sum</code>。你可以在构建查询后调用这些方法中的任何一个：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token keyword">use</span> <span class="token package">Illuminate<span class="token punctuation">\</span>Support<span class="token punctuation">\</span>Facades<span class="token punctuation">\</span>DB</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">count</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token variable">$price</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'orders'</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">max</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'price'</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>当然，你可以将这些方法与其他子句结合起来，以优化计算聚合值的方式：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$price</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'orders'</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">where</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'finalized'</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">avg</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'price'</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h4 id="判断记录是否存在" tabindex="-1"><a class="header-anchor" href="#判断记录是否存在"><span>判断记录是否存在</span></a></h4>
<p>除了通过 <code v-pre>count</code> 方法可以确定查询条件的结果是否存在之外，还可以使用 <code v-pre>exists</code> 和 <code v-pre>doesntExist</code> 方法：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token keyword">if</span> <span class="token punctuation">(</span><span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'orders'</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">where</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'finalized'</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">exists</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token punctuation">{</span></span>
<span class="line">    <span class="token comment">// ...</span></span>
<span class="line"><span class="token punctuation">}</span></span>
<span class="line"></span>
<span class="line"><span class="token keyword">if</span> <span class="token punctuation">(</span><span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'orders'</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">where</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'finalized'</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">doesntExist</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token punctuation">{</span></span>
<span class="line">    <span class="token comment">// ...</span></span>
<span class="line"><span class="token punctuation">}</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h2 id="select-语句" tabindex="-1"><a class="header-anchor" href="#select-语句"><span>Select 语句</span></a></h2>
<h4 id="指定一个-select-语句" tabindex="-1"><a class="header-anchor" href="#指定一个-select-语句"><span>指定一个 Select 语句</span></a></h4>
<p>可能你并不总是希望从数据库表中获取所有列。 使用 <code v-pre>select</code> 方法，可以自定义一个 「select」 查询语句来查询指定的字段：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token keyword">use</span> <span class="token package">Illuminate<span class="token punctuation">\</span>Support<span class="token punctuation">\</span>Facades<span class="token punctuation">\</span>DB</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span></span>
<span class="line">            <span class="token operator">-></span><span class="token function">select</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'name'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'email as user_email'</span><span class="token punctuation">)</span></span>
<span class="line">            <span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p><code v-pre>distinct</code> 方法会强制让查询返回的结果不重复：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">distinct</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div></div></div><p>如果你已经有了一个查询构造器实例，并且希望在现有的查询语句中加入一个字段，那么你可以使用 <code v-pre>addSelect</code> 方法：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$query</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">select</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'name'</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token variable">$query</span><span class="token operator">-></span><span class="token function">addSelect</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'age'</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h2 id="原生表达式" tabindex="-1"><a class="header-anchor" href="#原生表达式"><span>原生表达式</span></a></h2>
<p>当你需要在查询中插入任意的字符串时，你可以使用 <code v-pre>DB</code> 门面提供的 <code v-pre>raw</code> 方法以创建原生表达式。</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span></span>
<span class="line">             <span class="token operator">-></span><span class="token function">select</span><span class="token punctuation">(</span><span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">raw</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'count(*) as user_count, status'</span><span class="token punctuation">)</span><span class="token punctuation">)</span></span>
<span class="line">             <span class="token operator">-></span><span class="token function">where</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'status'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'&lt;>'</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">)</span></span>
<span class="line">             <span class="token operator">-></span><span class="token function">groupBy</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'status'</span><span class="token punctuation">)</span></span>
<span class="line">             <span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><blockquote>
<p><strong>警告</strong><br>
原生语句作为字符串注入到查询中，因此必须格外小心避免产生 SQL 注入漏洞。</p>
</blockquote>
<h3 id="原生方法。" tabindex="-1"><a class="header-anchor" href="#原生方法。"><span>原生方法。</span></a></h3>
<p>可以使用以下方法代替 <code v-pre>DB::raw</code>，将原生表达式插入查询的各个部分。<strong>请记住，Laravel 无法保证所有使用原生表达式的查询都不受到 SQL 注入漏洞的影响。</strong></p>
<h4 id="selectraw" tabindex="-1"><a class="header-anchor" href="#selectraw"><span><code v-pre>selectRaw</code></span></a></h4>
<p><code v-pre>selectRaw</code> 方法可以用来代替 <code v-pre>addSelect(DB::raw(/* ... */))</code>。此方法接受一个可选的绑定数组作为其第二个参数：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$orders</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'orders'</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">selectRaw</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'price * ? as price_with_tax'</span><span class="token punctuation">,</span> <span class="token punctuation">[</span><span class="token number">1.0825</span><span class="token punctuation">]</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h4 id="whereraw-orwhereraw" tabindex="-1"><a class="header-anchor" href="#whereraw-orwhereraw"><span><code v-pre>whereRaw / orWhereRaw</code></span></a></h4>
<p><code v-pre>whereRaw</code> 和 <code v-pre>orWhereRaw</code> 方法可用于将原始 「where」子句注入你的查询。这些方法接受一个可选的绑定数组作为它们的第二个参数：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$orders</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'orders'</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">whereRaw</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'price > IF(state = "TX", ?, 100)'</span><span class="token punctuation">,</span> <span class="token punctuation">[</span><span class="token number">200</span><span class="token punctuation">]</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h4 id="havingraw-orhavingraw" tabindex="-1"><a class="header-anchor" href="#havingraw-orhavingraw"><span><code v-pre>havingRaw / orHavingRaw</code></span></a></h4>
<p><code v-pre>havingRaw</code> 和 <code v-pre>orHavingRaw</code> 方法可用于提供原始字符串作为 「having」子句的值。这些方法接受一个可选的绑定数组作为它们的第二个参数：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$orders</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'orders'</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">select</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'department'</span><span class="token punctuation">,</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">raw</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'SUM(price) as total_sales'</span><span class="token punctuation">)</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">groupBy</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'department'</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">havingRaw</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'SUM(price) > ?'</span><span class="token punctuation">,</span> <span class="token punctuation">[</span><span class="token number">2500</span><span class="token punctuation">]</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h4 id="orderbyraw" tabindex="-1"><a class="header-anchor" href="#orderbyraw"><span><code v-pre>orderByRaw</code></span></a></h4>
<p>orderByRaw 方法可用于将原生字符串设置为「order by」子句的值：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$orders</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'orders'</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">orderByRaw</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'updated_at - created_at DESC'</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h3 id="groupbyraw" tabindex="-1"><a class="header-anchor" href="#groupbyraw"><span><code v-pre>groupByRaw</code></span></a></h3>
<p>groupByRaw 方法可以用于将原生字符串设置为 <code v-pre>group by</code> 子句的值：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$orders</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'orders'</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">select</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'city'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'state'</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">groupByRaw</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'city, state'</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h2 id="joins" tabindex="-1"><a class="header-anchor" href="#joins"><span>Joins</span></a></h2>
<h4 id="inner-join-语句" tabindex="-1"><a class="header-anchor" href="#inner-join-语句"><span>Inner Join 语句</span></a></h4>
<p>查询构造器也还可用于向查询中添加连接子句。若要执行基本的「inner join」，你可以对查询构造器实例使用 <code v-pre>join</code> 方法。传递给 <code v-pre>join</code> 方法的第一个参数是需要你连接到的表的名称，而其余参数指定连接的列约束。你甚至还可以在一个查询中连接多个表：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token keyword">use</span> <span class="token package">Illuminate<span class="token punctuation">\</span>Support<span class="token punctuation">\</span>Facades<span class="token punctuation">\</span>DB</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span></span>
<span class="line">            <span class="token operator">-></span><span class="token function">join</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'contacts'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'users.id'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'='</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'contacts.user_id'</span><span class="token punctuation">)</span></span>
<span class="line">            <span class="token operator">-></span><span class="token function">join</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'orders'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'users.id'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'='</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'orders.user_id'</span><span class="token punctuation">)</span></span>
<span class="line">            <span class="token operator">-></span><span class="token function">select</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users.*'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'contacts.phone'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'orders.price'</span><span class="token punctuation">)</span></span>
<span class="line">            <span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h4 id="left-join-right-join-语句" tabindex="-1"><a class="header-anchor" href="#left-join-right-join-语句"><span>Left Join / Right Join 语句</span></a></h4>
<p>如果你想使用「left join」或者「right join」代替「inner join」，可以使用 <code v-pre>leftJoin</code> 或者 <code v-pre>rightJoin</code> 方法。这两个方法与 <code v-pre>join</code> 方法用法相同：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span></span>
<span class="line">            <span class="token operator">-></span><span class="token function">leftJoin</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'posts'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'users.id'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'='</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'posts.user_id'</span><span class="token punctuation">)</span></span>
<span class="line">            <span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span></span>
<span class="line">            <span class="token operator">-></span><span class="token function">rightJoin</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'posts'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'users.id'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'='</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'posts.user_id'</span><span class="token punctuation">)</span></span>
<span class="line">            <span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h4 id="cross-join-语句" tabindex="-1"><a class="header-anchor" href="#cross-join-语句"><span>Cross Join 语句</span></a></h4>
<p>你可以使用 <code v-pre>crossJoin</code> 方法执行「交叉连接」。交叉连接在第一个表和被连接的表之间会生成笛卡尔积：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$sizes</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'sizes'</span><span class="token punctuation">)</span></span>
<span class="line">            <span class="token operator">-></span><span class="token function">crossJoin</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'colors'</span><span class="token punctuation">)</span></span>
<span class="line">            <span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h4 id="高级-join-语句" tabindex="-1"><a class="header-anchor" href="#高级-join-语句"><span>高级 Join 语句</span></a></h4>
<p>你还可以指定更高级的联接子句。首先，将闭包作为第二个参数传递给 <code v-pre>join</code> 方法。闭包将收到一个 <code v-pre>Illuminate\Database\Query\JoinClause</code> 实例，该实例允许你指定对 <code v-pre>join</code> 子句的约束：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span></span>
<span class="line">        <span class="token operator">-></span><span class="token function">join</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'contacts'</span><span class="token punctuation">,</span> <span class="token keyword">function</span> <span class="token punctuation">(</span><span class="token class-name type-declaration">JoinClause</span> <span class="token variable">$join</span><span class="token punctuation">)</span> <span class="token punctuation">{</span></span>
<span class="line">            <span class="token variable">$join</span><span class="token operator">-></span><span class="token function">on</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users.id'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'='</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'contacts.user_id'</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">orOn</span><span class="token punctuation">(</span><span class="token comment">/* ... */</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line">        <span class="token punctuation">}</span><span class="token punctuation">)</span></span>
<span class="line">        <span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>如果你想要在连接上使用「where」风格的语句，你可以在连接上使用 <code v-pre>JoinClause</code> 实例中的 <code v-pre>where</code> 和 <code v-pre>orWhere</code> 方法。这些方法会将列和值进行比较，而不是列和列进行比较：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span></span>
<span class="line">        <span class="token operator">-></span><span class="token function">join</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'contacts'</span><span class="token punctuation">,</span> <span class="token keyword">function</span> <span class="token punctuation">(</span><span class="token class-name type-declaration">JoinClause</span> <span class="token variable">$join</span><span class="token punctuation">)</span> <span class="token punctuation">{</span></span>
<span class="line">            <span class="token variable">$join</span><span class="token operator">-></span><span class="token function">on</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users.id'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'='</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'contacts.user_id'</span><span class="token punctuation">)</span></span>
<span class="line">                 <span class="token operator">-></span><span class="token function">where</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'contacts.user_id'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'>'</span><span class="token punctuation">,</span> <span class="token number">5</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line">        <span class="token punctuation">}</span><span class="token punctuation">)</span></span>
<span class="line">        <span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h4 id="子连接查询" tabindex="-1"><a class="header-anchor" href="#子连接查询"><span>子连接查询</span></a></h4>
<p>你可以使用 <code v-pre>joinSub</code>，<code v-pre>leftJoinSub</code> 和 <code v-pre>rightJoinSub</code> 方法关联一个查询作为子查询。他们每一种方法都会接收三个参数：子查询、表别名和定义关联字段的闭包。如下面这个例子，获取含有用户最近一次发布博客时的 <code v-pre>created_at</code> 时间戳的用户集合：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$latestPosts</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'posts'</span><span class="token punctuation">)</span></span>
<span class="line">                   <span class="token operator">-></span><span class="token function">select</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'user_id'</span><span class="token punctuation">,</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">raw</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'MAX(created_at) as last_post_created_at'</span><span class="token punctuation">)</span><span class="token punctuation">)</span></span>
<span class="line">                   <span class="token operator">-></span><span class="token function">where</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'is_published'</span><span class="token punctuation">,</span> <span class="token constant boolean">true</span><span class="token punctuation">)</span></span>
<span class="line">                   <span class="token operator">-></span><span class="token function">groupBy</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'user_id'</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span></span>
<span class="line">        <span class="token operator">-></span><span class="token function">joinSub</span><span class="token punctuation">(</span><span class="token variable">$latestPosts</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'latest_posts'</span><span class="token punctuation">,</span> <span class="token keyword">function</span> <span class="token punctuation">(</span><span class="token class-name type-declaration">JoinClause</span> <span class="token variable">$join</span><span class="token punctuation">)</span> <span class="token punctuation">{</span></span>
<span class="line">            <span class="token variable">$join</span><span class="token operator">-></span><span class="token function">on</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users.id'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'='</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'latest_posts.user_id'</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line">        <span class="token punctuation">}</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h2 id="联合" tabindex="-1"><a class="header-anchor" href="#联合"><span>联合</span></a></h2>
<p>查询构造器还提供了一种简洁的方式将两个或者多个查询「联合」在一起。例如，你可以先创建一个查询，然后使用 <code v-pre>union</code> 方法来连接更多的查询：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token keyword">use</span> <span class="token package">Illuminate<span class="token punctuation">\</span>Support<span class="token punctuation">\</span>Facades<span class="token punctuation">\</span>DB</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token variable">$first</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span></span>
<span class="line">            <span class="token operator">-></span><span class="token function">whereNull</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'first_name'</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span></span>
<span class="line">            <span class="token operator">-></span><span class="token function">whereNull</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'last_name'</span><span class="token punctuation">)</span></span>
<span class="line">            <span class="token operator">-></span><span class="token function">union</span><span class="token punctuation">(</span><span class="token variable">$first</span><span class="token punctuation">)</span></span>
<span class="line">            <span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>查询构造器不仅提供了 <code v-pre>union</code> 方法，还提供了一个 <code v-pre>unionAll</code> 方法。当查询结合 <code v-pre>unionAll</code> 方法使用时，将不会删除重复的结果。<code v-pre>unionAll</code> 方法的用法和 <code v-pre>union</code> 方法一样。</p>
<h2 id="基础的-where-语句" tabindex="-1"><a class="header-anchor" href="#基础的-where-语句"><span>基础的 Where 语句</span></a></h2>
<h3 id="where-语句" tabindex="-1"><a class="header-anchor" href="#where-语句"><span>Where 语句</span></a></h3>
<p>你可以在 <code v-pre>where</code> 语句中使用查询构造器的 <code v-pre>where</code> 方法。调用 <code v-pre>where</code> 方法需要三个基本参数。第一个参数是字段的名称。第二个参数是一个操作符，它可以是数据库中支持的任意操作符。第三个参数是与字段比较的值。</p>
<p>例如。在 <code v-pre>users</code> 表中查询 <code v-pre>votes</code> 字段等于 <code v-pre>100</code> 并且 <code v-pre>age</code> 字段大于 <code v-pre>35</code> 的数据：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">where</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'votes'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'='</span><span class="token punctuation">,</span> <span class="token number">100</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">where</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'age'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'>'</span><span class="token punctuation">,</span> <span class="token number">35</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>为了方便起见。如果你想要比较一个字段的值是否 <code v-pre>等于</code> 给定的值。你可以将这个给定的值作为第二个参数传递给 <code v-pre>where</code> 方法。那么，Laravel 会默认使用 <code v-pre>=</code> 操作符：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">where</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'votes'</span><span class="token punctuation">,</span> <span class="token number">100</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div></div></div><p>如上所述，你可以使用数据库支持的任意操作符：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">where</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'votes'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'>='</span><span class="token punctuation">,</span> <span class="token number">100</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">where</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'votes'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'&lt;>'</span><span class="token punctuation">,</span> <span class="token number">100</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">where</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'name'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'like'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'T%'</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>你也可以将一个条件数组传递给 <code v-pre>where</code> 方法。数组的每个元素都应该是一个数组，其中包是传递给 <code v-pre>where</code> 方法的三个参数：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">where</span><span class="token punctuation">(</span><span class="token punctuation">[</span></span>
<span class="line">    <span class="token punctuation">[</span><span class="token string single-quoted-string">'status'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'='</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'1'</span><span class="token punctuation">]</span><span class="token punctuation">,</span></span>
<span class="line">    <span class="token punctuation">[</span><span class="token string single-quoted-string">'subscribed'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'&lt;>'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'1'</span><span class="token punctuation">]</span><span class="token punctuation">,</span></span>
<span class="line"><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><blockquote>
<p><strong>注意</strong><br>
PDO 不支持绑定字段名。因此，你不应该允许让用户输入字段名进行查询引用，包括结果集「order by」语句。</p>
</blockquote>
<h3 id="or-where-语句" tabindex="-1"><a class="header-anchor" href="#or-where-语句"><span>Or Where 语句</span></a></h3>
<p>当链式调用多个 <code v-pre>where</code> 方法的时候，这些「where」语句将会被看成是 <code v-pre>and</code> 关系。另外，你也可以在查询语句中使用 <code v-pre>orWhere</code> 方法来表示 <code v-pre>or</code> 关系。orWhere 方法接收的参数和 where 方法接收的参数一样：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span></span>
<span class="line">                    <span class="token operator">-></span><span class="token function">where</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'votes'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'>'</span><span class="token punctuation">,</span> <span class="token number">100</span><span class="token punctuation">)</span></span>
<span class="line">                    <span class="token operator">-></span><span class="token function">orWhere</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'name'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'John'</span><span class="token punctuation">)</span></span>
<span class="line">                    <span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>如果你需要在括号内对 「or」 条件进行分组，那么可以传递一个闭包作为 <code v-pre>orWhere</code> 方法的第一个参数：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span></span>
<span class="line">            <span class="token operator">-></span><span class="token function">where</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'votes'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'>'</span><span class="token punctuation">,</span> <span class="token number">100</span><span class="token punctuation">)</span></span>
<span class="line">            <span class="token operator">-></span><span class="token function">orWhere</span><span class="token punctuation">(</span><span class="token keyword">function</span><span class="token punctuation">(</span><span class="token class-name type-declaration">Builder</span> <span class="token variable">$query</span><span class="token punctuation">)</span> <span class="token punctuation">{</span></span>
<span class="line">                <span class="token variable">$query</span><span class="token operator">-></span><span class="token function">where</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'name'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'Abigail'</span><span class="token punctuation">)</span></span>
<span class="line">                      <span class="token operator">-></span><span class="token function">where</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'votes'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'>'</span><span class="token punctuation">,</span> <span class="token number">50</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line">            <span class="token punctuation">}</span><span class="token punctuation">)</span></span>
<span class="line">            <span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>上面的示例将生成以下 SQL：</p>
<div class="language-sql line-numbers-mode" data-highlighter="prismjs" data-ext="sql" data-title="sql"><pre v-pre class="language-sql"><code><span class="line"><span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> users <span class="token keyword">where</span> votes <span class="token operator">></span> <span class="token number">100</span> <span class="token operator">or</span> <span class="token punctuation">(</span>name <span class="token operator">=</span> <span class="token string">'Abigail'</span> <span class="token operator">and</span> votes <span class="token operator">></span> <span class="token number">50</span><span class="token punctuation">)</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div></div></div><blockquote>
<p><strong>注意</strong><br>
为避免全局作用域应用时出现意外，你应始终对 <code v-pre>orWhere</code> 调用进行分组。</p>
</blockquote>
<h3 id="where-not-语句" tabindex="-1"><a class="header-anchor" href="#where-not-语句"><span>Where Not 语句</span></a></h3>
<p><code v-pre>whereNot</code> 和 <code v-pre>orWhereNot</code> 方法可用于否定一组给定的查询条件。例如, 下面的查询排除了正在清仓甩卖或价格低于 10 的产品：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$products</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'products'</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">whereNot</span><span class="token punctuation">(</span><span class="token keyword">function</span> <span class="token punctuation">(</span><span class="token class-name type-declaration">Builder</span> <span class="token variable">$query</span><span class="token punctuation">)</span> <span class="token punctuation">{</span></span>
<span class="line">                    <span class="token variable">$query</span><span class="token operator">-></span><span class="token function">where</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'clearance'</span><span class="token punctuation">,</span> <span class="token constant boolean">true</span><span class="token punctuation">)</span></span>
<span class="line">                          <span class="token operator">-></span><span class="token function">orWhere</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'price'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'&lt;'</span><span class="token punctuation">,</span> <span class="token number">10</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line">                <span class="token punctuation">}</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h3 id="json-where-语句" tabindex="-1"><a class="header-anchor" href="#json-where-语句"><span>JSON Where 语句</span></a></h3>
<p>Laravel 也支持 JSON 类型的字段查询，前提是数据库也支持 JSON 类型。目前，有 MySQL 5.7+、PostgreSQL、SQL Server 2016 和 SQLite 3.39.0 支持 JSON 类型 (with the <a href="https://www.sqlite.org/json1.html" target="_blank" rel="noopener noreferrer">JSON1 extension</a>)。可以使用 <code v-pre>-&gt;</code> 操作符来查询 JSON 字段：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">where</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'preferences->dining->meal'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'salad'</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>你可以使用 <code v-pre>whereJsonContains</code> 方法来查询 JSON 数组。但是 SQLite 数据库版本低于3.38.0时不支持该功能：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">whereJsonContains</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'options->languages'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'en'</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>如果你的应用使用的是 MySQL 或者 PostgreSQL 数据库，那么你可以向 <code v-pre>whereJsonContains</code> 方法中传递一个数组类型的值：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">whereJsonContains</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'options->languages'</span><span class="token punctuation">,</span> <span class="token punctuation">[</span><span class="token string single-quoted-string">'en'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'de'</span><span class="token punctuation">]</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>你可以使用 <code v-pre>whereJsonLength</code> 方法来查询 JSON 数组的长度：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">whereJsonLength</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'options->languages'</span><span class="token punctuation">,</span> <span class="token number">0</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">whereJsonLength</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'options->languages'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'>'</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h3 id="其他-where-语句" tabindex="-1"><a class="header-anchor" href="#其他-where-语句"><span>其他 Where 语句</span></a></h3>
<p><strong>whereBetween / orWhereBetween</strong></p>
<p><code v-pre>whereBetween</code> 方法是用来验证字段的值是否在给定的两个值之间：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span></span>
<span class="line">           <span class="token operator">-></span><span class="token function">whereBetween</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'votes'</span><span class="token punctuation">,</span> <span class="token punctuation">[</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">100</span><span class="token punctuation">]</span><span class="token punctuation">)</span></span>
<span class="line">           <span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p><strong>whereNotBetween / orWhereNotBetween</strong></p>
<p><code v-pre>whereNotBetween</code>方法用于验证字段的值是否不在给定的两个值范围之中：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span></span>
<span class="line">                    <span class="token operator">-></span><span class="token function">whereNotBetween</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'votes'</span><span class="token punctuation">,</span> <span class="token punctuation">[</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">100</span><span class="token punctuation">]</span><span class="token punctuation">)</span></span>
<span class="line">                    <span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p><strong>whereBetweenColumns / whereNotBetweenColumns / orWhereBetweenColumns / orWhereNotBetweenColumns</strong></p>
<p><code v-pre>whereBetweenColumns</code> 方法用于验证字段是否在给定的两个字段的值的范围中：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$patients</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'patients'</span><span class="token punctuation">)</span></span>
<span class="line">                       <span class="token operator">-></span><span class="token function">whereBetweenColumns</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'weight'</span><span class="token punctuation">,</span> <span class="token punctuation">[</span><span class="token string single-quoted-string">'minimum_allowed_weight'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'maximum_allowed_weight'</span><span class="token punctuation">]</span><span class="token punctuation">)</span></span>
<span class="line">                       <span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p><code v-pre>whereNotBetweenColumns</code> 方法用于验证字段是否不在给定的两个字段的值的范围中：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$patients</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'patients'</span><span class="token punctuation">)</span></span>
<span class="line">                       <span class="token operator">-></span><span class="token function">whereNotBetweenColumns</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'weight'</span><span class="token punctuation">,</span> <span class="token punctuation">[</span><span class="token string single-quoted-string">'minimum_allowed_weight'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'maximum_allowed_weight'</span><span class="token punctuation">]</span><span class="token punctuation">)</span></span>
<span class="line">                       <span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p><strong>whereIn / whereNotIn / orWhereIn / orWhereNotIn</strong></p>
<p><code v-pre>whereIn</code> 方法用于验证字段是否在给定的值数组中：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span></span>
<span class="line">                    <span class="token operator">-></span><span class="token function">whereIn</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'id'</span><span class="token punctuation">,</span> <span class="token punctuation">[</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">2</span><span class="token punctuation">,</span> <span class="token number">3</span><span class="token punctuation">]</span><span class="token punctuation">)</span></span>
<span class="line">                    <span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p><code v-pre>whereIn</code> 方法用于验证字段是否不在给定的值数组中：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span></span>
<span class="line">                    <span class="token operator">-></span><span class="token function">whereNotIn</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'id'</span><span class="token punctuation">,</span> <span class="token punctuation">[</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">2</span><span class="token punctuation">,</span> <span class="token number">3</span><span class="token punctuation">]</span><span class="token punctuation">)</span></span>
<span class="line">                    <span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>你也可以为<code v-pre>whereIn</code> 方法的第二个参数提供一个子查询：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$activeUsers</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">select</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'id'</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">where</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'is_active'</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'comments'</span><span class="token punctuation">)</span></span>
<span class="line">                    <span class="token operator">-></span><span class="token function">whereIn</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'user_id'</span><span class="token punctuation">,</span> <span class="token variable">$activeUsers</span><span class="token punctuation">)</span></span>
<span class="line">                    <span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>上面的例子将会转换为下面的 SQL 查询语句：</p>
<div class="language-sql line-numbers-mode" data-highlighter="prismjs" data-ext="sql" data-title="sql"><pre v-pre class="language-sql"><code><span class="line"><span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> comments <span class="token keyword">where</span> user_id <span class="token operator">in</span> <span class="token punctuation">(</span></span>
<span class="line">    <span class="token keyword">select</span> id</span>
<span class="line">    <span class="token keyword">from</span> users</span>
<span class="line">    <span class="token keyword">where</span> is_active <span class="token operator">=</span> <span class="token number">1</span></span>
<span class="line"><span class="token punctuation">)</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><blockquote>
<p><strong>注意</strong><br>
如果你需要判断一个整数的大数组 <code v-pre>whereIntegerInRaw</code> 或 <code v-pre>whereIntegerNotInRaw</code>方法可能会更适合，这种用法的内存占用更小。</p>
</blockquote>
<p><strong>whereNull / whereNotNull / orWhereNull / orWhereNotNull</strong></p>
<p><code v-pre>whereNull</code> 方法用于判断指定的字段的值是否是<code v-pre>NULL</code>：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">whereNull</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'updated_at'</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p><code v-pre>whereNotNull</code> 方法是用来验证给定字段的值是否不为 <code v-pre>NULL</code>:</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">whereNotNull</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'updated_at'</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p><strong>whereDate / whereMonth / whereDay / whereYear / whereTime</strong></p>
<p><code v-pre>whereDate</code> 方法是用来比较字段的值与给定的日期值是否相等:</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">whereDate</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'created_at'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'2016-12-31'</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p><code v-pre>whereMonth</code> 方法是用来比较字段的值与给定的月是否相等:</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">whereMonth</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'created_at'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'12'</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p><code v-pre>whereDay</code> 方法是用来比较字段的值与给定的日是否相等:</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">whereDay</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'created_at'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'31'</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p><code v-pre>whereYear</code> 方法是用来比较字段的值与给定的年是否相等:</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">whereYear</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'created_at'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'2016'</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p><code v-pre>whereTime</code> 方法是用来比较字段的值与给定的时间是否相等:</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">whereTime</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'created_at'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'='</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'11:20:45'</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p><strong>whereColumn / orWhereColumn</strong></p>
<p><code v-pre>whereColumn</code> 方法是用来比较两个给定字段的值是否相等:</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">whereColumn</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'first_name'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'last_name'</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>你也可以将比较运算符传递给 <code v-pre>whereColumn</code> 方法:</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">whereColumn</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'updated_at'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'>'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'created_at'</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>你还可以向 <code v-pre>whereColumn</code> 方法中传递一个数组。这些条件将使用 <code v-pre>and</code> 运算符联接:</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">whereColumn</span><span class="token punctuation">(</span><span class="token punctuation">[</span></span>
<span class="line">                    <span class="token punctuation">[</span><span class="token string single-quoted-string">'first_name'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'='</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'last_name'</span><span class="token punctuation">]</span><span class="token punctuation">,</span></span>
<span class="line">                    <span class="token punctuation">[</span><span class="token string single-quoted-string">'updated_at'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'>'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'created_at'</span><span class="token punctuation">]</span><span class="token punctuation">,</span></span>
<span class="line">                <span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h3 id="逻辑分组" tabindex="-1"><a class="header-anchor" href="#逻辑分组"><span>逻辑分组</span></a></h3>
<p>有时你可能需要将括号内的几个「where」子句分组，以实现查询所需的逻辑分组。实际上应该将 <code v-pre>orWhere</code> 方法的调用分组到括号中，以避免不可预料的查询逻辑误差。因此可以传递闭包给 <code v-pre>where</code> 方法：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span></span>
<span class="line">           <span class="token operator">-></span><span class="token function">where</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'name'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'='</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'John'</span><span class="token punctuation">)</span></span>
<span class="line">           <span class="token operator">-></span><span class="token function">where</span><span class="token punctuation">(</span><span class="token keyword">function</span> <span class="token punctuation">(</span><span class="token class-name type-declaration">Builder</span> <span class="token variable">$query</span><span class="token punctuation">)</span> <span class="token punctuation">{</span></span>
<span class="line">               <span class="token variable">$query</span><span class="token operator">-></span><span class="token function">where</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'votes'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'>'</span><span class="token punctuation">,</span> <span class="token number">100</span><span class="token punctuation">)</span></span>
<span class="line">                     <span class="token operator">-></span><span class="token function">orWhere</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'title'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'='</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'Admin'</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line">           <span class="token punctuation">}</span><span class="token punctuation">)</span></span>
<span class="line">           <span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>你可以看到， 通过一个闭包写入 <code v-pre>where</code> 方法 构建一个查询构造器来约束一个分组。这个闭包接收一个查询实例，你可以使用这个实例来设置应该包含的约束。上面的例子将生成以下 SQL：</p>
<div class="language-sql line-numbers-mode" data-highlighter="prismjs" data-ext="sql" data-title="sql"><pre v-pre class="language-sql"><code><span class="line"><span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> users <span class="token keyword">where</span> name <span class="token operator">=</span> <span class="token string">'John'</span> <span class="token operator">and</span> <span class="token punctuation">(</span>votes <span class="token operator">></span> <span class="token number">100</span> <span class="token operator">or</span> title <span class="token operator">=</span> <span class="token string">'Admin'</span><span class="token punctuation">)</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div></div></div><blockquote>
<p><strong>注意</strong><br>
你应该用 <code v-pre>orWhere</code> 调用这个分组，以避免应用全局作用时出现意外。</p>
</blockquote>
<h3 id="高级-where-语句" tabindex="-1"><a class="header-anchor" href="#高级-where-语句"><span>高级 Where 语句</span></a></h3>
<h3 id="where-exists-语句" tabindex="-1"><a class="header-anchor" href="#where-exists-语句"><span>Where Exists 语句</span></a></h3>
<p><code v-pre>whereExists</code> 方法允许你使用 <code v-pre>where exists</code> SQL 语句。 <code v-pre>whereExists</code> 方法接收一个闭包参数，该闭包获取一个查询构建器实例，从而允许你定义放置在 <code v-pre>exists</code> 子句中查询:</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span></span>
<span class="line">           <span class="token operator">-></span><span class="token function">whereExists</span><span class="token punctuation">(</span><span class="token keyword">function</span> <span class="token punctuation">(</span><span class="token class-name type-declaration">Builder</span> <span class="token variable">$query</span><span class="token punctuation">)</span> <span class="token punctuation">{</span></span>
<span class="line">               <span class="token variable">$query</span><span class="token operator">-></span><span class="token function">select</span><span class="token punctuation">(</span><span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">raw</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">)</span></span>
<span class="line">                     <span class="token operator">-></span><span class="token function">from</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'orders'</span><span class="token punctuation">)</span></span>
<span class="line">                     <span class="token operator">-></span><span class="token function">whereColumn</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'orders.user_id'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'users.id'</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line">           <span class="token punctuation">}</span><span class="token punctuation">)</span></span>
<span class="line">           <span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>或者，可以向 <code v-pre>whereExists</code> 方法提供一个查询对象，替换上面的闭包：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$orders</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'orders'</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">select</span><span class="token punctuation">(</span><span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">raw</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">whereColumn</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'orders.user_id'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'users.id'</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span></span>
<span class="line">                    <span class="token operator">-></span><span class="token function">whereExists</span><span class="token punctuation">(</span><span class="token variable">$orders</span><span class="token punctuation">)</span></span>
<span class="line">                    <span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>上面的两个示例都会生成如下的 <code v-pre>SQL</code> 语句</p>
<div class="language-sql line-numbers-mode" data-highlighter="prismjs" data-ext="sql" data-title="sql"><pre v-pre class="language-sql"><code><span class="line"><span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> users</span>
<span class="line"><span class="token keyword">where</span> <span class="token keyword">exists</span> <span class="token punctuation">(</span></span>
<span class="line">    <span class="token keyword">select</span> <span class="token number">1</span></span>
<span class="line">    <span class="token keyword">from</span> orders</span>
<span class="line">    <span class="token keyword">where</span> orders<span class="token punctuation">.</span>user_id <span class="token operator">=</span> users<span class="token punctuation">.</span>id</span>
<span class="line"><span class="token punctuation">)</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h3 id="子查询-where-语句" tabindex="-1"><a class="header-anchor" href="#子查询-where-语句"><span>子查询 Where 语句</span></a></h3>
<p>有时候，你可能需要构造一个 <code v-pre>where</code> 子查询，将子查询的结果与给定的值进行比较。你可以通过向 <code v-pre>where</code> 方法传递闭包和值来实现此操作。例如，下面的查询将检索最后一次「会员」购买记录是「Pro」类型的所有用户；</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token keyword">use</span> <span class="token package">App<span class="token punctuation">\</span>Models<span class="token punctuation">\</span>User</span><span class="token punctuation">;</span></span>
<span class="line"><span class="token keyword">use</span> <span class="token package">Illuminate<span class="token punctuation">\</span>Database<span class="token punctuation">\</span>Query<span class="token punctuation">\</span>Builder</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token class-name static-context">User</span><span class="token operator">::</span><span class="token function">where</span><span class="token punctuation">(</span><span class="token keyword">function</span> <span class="token punctuation">(</span><span class="token class-name type-declaration">Builder</span> <span class="token variable">$query</span><span class="token punctuation">)</span> <span class="token punctuation">{</span></span>
<span class="line">    <span class="token variable">$query</span><span class="token operator">-></span><span class="token function">select</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'type'</span><span class="token punctuation">)</span></span>
<span class="line">        <span class="token operator">-></span><span class="token function">from</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'membership'</span><span class="token punctuation">)</span></span>
<span class="line">        <span class="token operator">-></span><span class="token function">whereColumn</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'membership.user_id'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'users.id'</span><span class="token punctuation">)</span></span>
<span class="line">        <span class="token operator">-></span><span class="token function">orderByDesc</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'membership.start_date'</span><span class="token punctuation">)</span></span>
<span class="line">        <span class="token operator">-></span><span class="token function">limit</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"><span class="token punctuation">}</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'Pro'</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>或者，你可能需要构建一个 <code v-pre>where</code> 子句，将列与子查询的结果进行比较。你可以通过将列、运算符和闭包传递给 <code v-pre>where</code> 方法来完成此操作。例如，以下查询将检索金额小于平均值的所有收入记录；</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token keyword">use</span> <span class="token package">App<span class="token punctuation">\</span>Models<span class="token punctuation">\</span>Income</span><span class="token punctuation">;</span></span>
<span class="line"><span class="token keyword">use</span> <span class="token package">Illuminate<span class="token punctuation">\</span>Database<span class="token punctuation">\</span>Query<span class="token punctuation">\</span>Builder</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token variable">$incomes</span> <span class="token operator">=</span> <span class="token class-name static-context">Income</span><span class="token operator">::</span><span class="token function">where</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'amount'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'&lt;'</span><span class="token punctuation">,</span> <span class="token keyword">function</span> <span class="token punctuation">(</span><span class="token class-name type-declaration">Builder</span> <span class="token variable">$query</span><span class="token punctuation">)</span> <span class="token punctuation">{</span></span>
<span class="line">    <span class="token variable">$query</span><span class="token operator">-></span><span class="token function">selectRaw</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'avg(i.amount)'</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">from</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'incomes as i'</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"><span class="token punctuation">}</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h3 id="全文-where-子句" tabindex="-1"><a class="header-anchor" href="#全文-where-子句"><span>全文 Where 子句</span></a></h3>
<blockquote>
<p><strong>注意</strong><br>
MySQL 和 PostgreSQL 目前支持全文 where 子句。</p>
</blockquote>
<p>可以使用 <code v-pre>where FullText</code> 和 <code v-pre>orWhere FullText</code> 方法将全文「where」 子句添加到具有 <a href="https://learnku.com/docs/laravel/10.x/migrations#available-index-types" target="_blank" rel="noopener noreferrer">full text indexes</a> 的列的查询中。这些方法将由Laravel转换为适用于底层数据库系统的SQL。例如，使用MySQL的应用会生成 <code v-pre>MATCH AGAINST</code> 子句</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span></span>
<span class="line">           <span class="token operator">-></span><span class="token function">whereFullText</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'bio'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'web developer'</span><span class="token punctuation">)</span></span>
<span class="line">           <span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h2 id="ordering-grouping-limit-offset" tabindex="-1"><a class="header-anchor" href="#ordering-grouping-limit-offset"><span>Ordering, Grouping, Limit &amp; Offset</span></a></h2>
<h3 id="排序" tabindex="-1"><a class="header-anchor" href="#排序"><span>排序</span></a></h3>
<h4 id="orderby-方法" tabindex="-1"><a class="header-anchor" href="#orderby-方法"><span><code v-pre>orderBy</code> 方法</span></a></h4>
<p><code v-pre>orderBy</code> 方法允许你按给定列对查询结果进行排序。<code v-pre>orderBy</code> 方法接受的第一个参数应该是你希望排序的列，而第二个参数确定排序的方向，可以是 <code v-pre>asc</code> 或 <code v-pre>desc</code>：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">orderBy</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'name'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'desc'</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>要按多列排序，你以根据需要多次调用 <code v-pre>orderBy</code>：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">orderBy</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'name'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'desc'</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">orderBy</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'email'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'asc'</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h4 id="latest-和-oldest-方法" tabindex="-1"><a class="header-anchor" href="#latest-和-oldest-方法"><span><code v-pre>latest</code> 和 <code v-pre>oldest</code> 方法</span></a></h4>
<p><code v-pre>latest</code> 和 <code v-pre>oldest</code> 方法可以方便让你把结果根据日期排序。查询结果默认根据数据表的 <code v-pre>created_at</code> 字段进行排序 。或者，你可以传一个你想要排序的列名，通过:</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$user</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">latest</span><span class="token punctuation">(</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">first</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h4 id="随机排序" tabindex="-1"><a class="header-anchor" href="#随机排序"><span>随机排序</span></a></h4>
<p><code v-pre>inRandomOrder</code> 方法被用来将查询结果随机排序。例如，你可以使用这个方法去获得一个随机用户:</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$randomUser</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">inRandomOrder</span><span class="token punctuation">(</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">first</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h4 id="移除已存在的排序" tabindex="-1"><a class="header-anchor" href="#移除已存在的排序"><span>移除已存在的排序</span></a></h4>
<p><code v-pre>reorder</code> 方法会移除之前已经被应用到查询里的排序:</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$query</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">orderBy</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'name'</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token variable">$unorderedUsers</span> <span class="token operator">=</span> <span class="token variable">$query</span><span class="token operator">-></span><span class="token function">reorder</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>当你调用 <code v-pre>reorder</code> 方法去移除所有已经存在的排序的时候，你可以传递一个列名和排序方式去重新排序整个查询:</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$query</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">orderBy</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'name'</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token variable">$usersOrderedByEmail</span> <span class="token operator">=</span> <span class="token variable">$query</span><span class="token operator">-></span><span class="token function">reorder</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'email'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'desc'</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h3 id="分组" tabindex="-1"><a class="header-anchor" href="#分组"><span>分组</span></a></h3>
<h4 id="groupby-和-having-方法" tabindex="-1"><a class="header-anchor" href="#groupby-和-having-方法"><span><code v-pre>groupBy</code> 和 <code v-pre>having</code> 方法</span></a></h4>
<p>如你所愿，<code v-pre>groupBy</code> 和 <code v-pre>having</code> 方法可以将查询结果分组。<code v-pre>having</code> 方法的使用方法类似于 <code v-pre>where</code> 方法:</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">groupBy</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'account_id'</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">having</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'account_id'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'>'</span><span class="token punctuation">,</span> <span class="token number">100</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>你可以使用 <code v-pre>havingBetween</code> 方法在一个给定的范围内去过滤结果:</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$report</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'orders'</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">selectRaw</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'count(id) as number_of_orders, customer_id'</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">groupBy</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'customer_id'</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">havingBetween</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'number_of_orders'</span><span class="token punctuation">,</span> <span class="token punctuation">[</span><span class="token number">5</span><span class="token punctuation">,</span> <span class="token number">15</span><span class="token punctuation">]</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>你可以传多个参数给 <code v-pre>groupBy</code> 方法将多列分组:</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">groupBy</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'first_name'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'status'</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">having</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'account_id'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'>'</span><span class="token punctuation">,</span> <span class="token number">100</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>想要构造更高级的 <code v-pre>having</code> 语句, 看 <a href="#raw-methods"><code v-pre>havingRaw</code></a> 方法。</p>
<h3 id="限制和偏移量" tabindex="-1"><a class="header-anchor" href="#限制和偏移量"><span>限制和偏移量</span></a></h3>
<h4 id="skip-和-take-方法" tabindex="-1"><a class="header-anchor" href="#skip-和-take-方法"><span><code v-pre>skip</code> 和 <code v-pre>take</code> 方法</span></a></h4>
<p>你可以使用 <code v-pre>skip</code> 和 <code v-pre>take</code> 方法去限制查询结果的返回数量或者在查询结果中跳过给定数量:</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">skip</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">take</span><span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div></div></div><p>或者，你可以使用 <code v-pre>limit</code> 和 <code v-pre>offset</code> 方法。这些方法在功能上等同于 <code v-pre>take</code> 和 <code v-pre>skip</code> 方法, 如下</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">offset</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">limit</span><span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h2 id="条件语句" tabindex="-1"><a class="header-anchor" href="#条件语句"><span>条件语句</span></a></h2>
<p>有时，可能希望根据另一个条件将某些查询子句应用于查询。例如，当传入 HTTP 请求有一个给定的值的时候你才需要使用一个<code v-pre>where</code> 语句。你可以使用 <code v-pre>when</code> 方法去实现:</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$role</span> <span class="token operator">=</span> <span class="token variable">$request</span><span class="token operator">-></span><span class="token keyword type-declaration">string</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'role'</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">when</span><span class="token punctuation">(</span><span class="token variable">$role</span><span class="token punctuation">,</span> <span class="token keyword">function</span> <span class="token punctuation">(</span><span class="token class-name type-declaration">Builder</span> <span class="token variable">$query</span><span class="token punctuation">,</span> <span class="token keyword type-hint">string</span> <span class="token variable">$role</span><span class="token punctuation">)</span> <span class="token punctuation">{</span></span>
<span class="line">                    <span class="token variable">$query</span><span class="token operator">-></span><span class="token function">where</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'role_id'</span><span class="token punctuation">,</span> <span class="token variable">$role</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line">                <span class="token punctuation">}</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p><code v-pre>when</code> 方法只有当第一个参数为 <code v-pre>true</code> 时才执行给定的闭包。如果第一个参数是 <code v-pre>false</code> ，闭包将不会被执行。因此，在上面的例子中，只有在传入的请求包含 <code v-pre>role</code> 字段且结果为 <code v-pre>true</code> 时，<code v-pre>when</code> 方法里的闭包才会被调用。</p>
<p>你可以将另一个闭包作为第三个参数传递给 <code v-pre>when</code> 方法。这个闭包则旨在第一个参数结果为 <code v-pre>false</code> 时才会执行。为了说明如何使用该功能，我们将使用它来配置查询的默认排序：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$sortByVotes</span> <span class="token operator">=</span> <span class="token variable">$request</span><span class="token operator">-></span><span class="token function">boolean</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'sort_by_votes'</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">when</span><span class="token punctuation">(</span><span class="token variable">$sortByVotes</span><span class="token punctuation">,</span> <span class="token keyword">function</span> <span class="token punctuation">(</span><span class="token class-name type-declaration">Builder</span> <span class="token variable">$query</span><span class="token punctuation">,</span> <span class="token keyword type-hint">bool</span> <span class="token variable">$sortByVotes</span><span class="token punctuation">)</span> <span class="token punctuation">{</span></span>
<span class="line">                    <span class="token variable">$query</span><span class="token operator">-></span><span class="token function">orderBy</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'votes'</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line">                <span class="token punctuation">}</span><span class="token punctuation">,</span> <span class="token keyword">function</span> <span class="token punctuation">(</span><span class="token class-name type-declaration">Builder</span> <span class="token variable">$query</span><span class="token punctuation">)</span> <span class="token punctuation">{</span></span>
<span class="line">                    <span class="token variable">$query</span><span class="token operator">-></span><span class="token function">orderBy</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'name'</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line">                <span class="token punctuation">}</span><span class="token punctuation">)</span></span>
<span class="line">                <span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h2 id="插入语句" tabindex="-1"><a class="header-anchor" href="#插入语句"><span>插入语句</span></a></h2>
<p>查询构造器也提供了一个 <code v-pre>insert</code> 方法来用于插入记录到数据库表中。<code v-pre>insert</code> 方法接受一个列名和值的数组：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">insert</span><span class="token punctuation">(</span><span class="token punctuation">[</span></span>
<span class="line">    <span class="token string single-quoted-string">'email'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'kayla@example.com'</span><span class="token punctuation">,</span></span>
<span class="line">    <span class="token string single-quoted-string">'votes'</span> <span class="token operator">=></span> <span class="token number">0</span></span>
<span class="line"><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>你可以通过传递一个二维数组来实现一次插入多条记录。每一个数组都代表了一个应当插入到数据表中的记录：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">insert</span><span class="token punctuation">(</span><span class="token punctuation">[</span></span>
<span class="line">    <span class="token punctuation">[</span><span class="token string single-quoted-string">'email'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'picard@example.com'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'votes'</span> <span class="token operator">=></span> <span class="token number">0</span><span class="token punctuation">]</span><span class="token punctuation">,</span></span>
<span class="line">    <span class="token punctuation">[</span><span class="token string single-quoted-string">'email'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'janeway@example.com'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'votes'</span> <span class="token operator">=></span> <span class="token number">0</span><span class="token punctuation">]</span><span class="token punctuation">,</span></span>
<span class="line"><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p><code v-pre>insertOrIgnore</code> 方法将会在插入数据库的时候忽略发生的错误。当使用该方法时，你应当注意，重复记录插入的错误和其他类型的错误都将被忽略，这取决于数据库引擎。例如， <code v-pre>insertOrIgnore</code> 将会 <a href="https://dev.mysql.com/doc/refman/en/sql-mode.html#ignore-effect-on-execution" target="_blank" rel="noopener noreferrer">绕过 MySQL 的严格模式</a> ：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">insertOrIgnore</span><span class="token punctuation">(</span><span class="token punctuation">[</span></span>
<span class="line">    <span class="token punctuation">[</span><span class="token string single-quoted-string">'id'</span> <span class="token operator">=></span> <span class="token number">1</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'email'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'sisko@example.com'</span><span class="token punctuation">]</span><span class="token punctuation">,</span></span>
<span class="line">    <span class="token punctuation">[</span><span class="token string single-quoted-string">'id'</span> <span class="token operator">=></span> <span class="token number">2</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'email'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'archer@example.com'</span><span class="token punctuation">]</span><span class="token punctuation">,</span></span>
<span class="line"><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p><code v-pre>insertUsing</code> 方法将在表中插入新记录，同时用子查询来确定应插入的数据：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'pruned_users'</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">insertUsing</span><span class="token punctuation">(</span><span class="token punctuation">[</span></span>
<span class="line">    <span class="token string single-quoted-string">'id'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'name'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'email'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'email_verified_at'</span></span>
<span class="line"><span class="token punctuation">]</span><span class="token punctuation">,</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">select</span><span class="token punctuation">(</span></span>
<span class="line">    <span class="token string single-quoted-string">'id'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'name'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'email'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'email_verified_at'</span></span>
<span class="line"><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">where</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'updated_at'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'&lt;='</span><span class="token punctuation">,</span> <span class="token function">now</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">subMonth</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h4 id="自增-ids" tabindex="-1"><a class="header-anchor" href="#自增-ids"><span>自增 IDs</span></a></h4>
<p>如果数据表有自增 ID ，使用 <code v-pre>insertGetId</code> 方法来插入记录可以返回 ID 值：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$id</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">insertGetId</span><span class="token punctuation">(</span></span>
<span class="line">    <span class="token punctuation">[</span><span class="token string single-quoted-string">'email'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'john@example.com'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'votes'</span> <span class="token operator">=></span> <span class="token number">0</span><span class="token punctuation">]</span></span>
<span class="line"><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><blockquote>
<p><strong>注意</strong><br>
当使用 PostgreSQL 时，<code v-pre>insertGetId</code> 方法将默认把 <code v-pre>id</code> 作为自动递增字段的名称。如果你要从其他「字段」来获取 ID ，则需要将字段名称作为第二个参数传递给 <code v-pre>insertGetId</code> 方法。</p>
</blockquote>
<h3 id="更新插入" tabindex="-1"><a class="header-anchor" href="#更新插入"><span>更新插入</span></a></h3>
<p><code v-pre>upsert</code> 方法是是插入不存在的记录和为已经存在记录更新值。该方法的第一个参数包含要插入或更新的值，而第二个参数列出了在关联表中唯一标识记录的列。 该方法的第三个也是最后一个参数是一个列数组，如果数据库中已经存在匹配的记录，则应该更新这些列：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'flights'</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">upsert</span><span class="token punctuation">(</span></span>
<span class="line">    <span class="token punctuation">[</span></span>
<span class="line">        <span class="token punctuation">[</span><span class="token string single-quoted-string">'departure'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'Oakland'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'destination'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'San Diego'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'price'</span> <span class="token operator">=></span> <span class="token number">99</span><span class="token punctuation">]</span><span class="token punctuation">,</span></span>
<span class="line">        <span class="token punctuation">[</span><span class="token string single-quoted-string">'departure'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'Chicago'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'destination'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'New York'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'price'</span> <span class="token operator">=></span> <span class="token number">150</span><span class="token punctuation">]</span></span>
<span class="line">    <span class="token punctuation">]</span><span class="token punctuation">,</span></span>
<span class="line">    <span class="token punctuation">[</span><span class="token string single-quoted-string">'departure'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'destination'</span><span class="token punctuation">]</span><span class="token punctuation">,</span></span>
<span class="line">    <span class="token punctuation">[</span><span class="token string single-quoted-string">'price'</span><span class="token punctuation">]</span></span>
<span class="line"><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>在上面的例子中，Laravel 会尝试插入两条记录。如果已经存在具有相同 <code v-pre>departure</code> 和 <code v-pre>destination</code> 列值的记录，Laravel 将更新该记录的 <code v-pre>price</code> 列。</p>
<blockquote>
<p><strong>注意</strong><br>
除 SQL Server 之外的所有数据库都要求 <code v-pre>upsert</code> 方法的第二个参数中的列具有「主」或「唯一」索引。 此外，MySQL 数据库驱动程序忽略 <code v-pre>upsert</code> 方法的第二个参数，并始终使用表的「主」和「唯一」索引来检测现有记录。</p>
</blockquote>
<h2 id="更新语句" tabindex="-1"><a class="header-anchor" href="#更新语句"><span>更新语句</span></a></h2>
<p>除了插入记录到数据库之外，查询构造器也可以使用 <code v-pre>update</code> 方法来更新已经存在的记录。<code v-pre>update</code> 方法像 <code v-pre>insert</code> 方法一样，接受一个列名和值的数组作为参数，它表示要更新的列和数据。<code v-pre>update</code> 方法返回受影响的行数。你可以使用 <code v-pre>where</code> 子句来限制 <code v-pre>update</code> 查询：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$affected</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span></span>
<span class="line">              <span class="token operator">-></span><span class="token function">where</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'id'</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">)</span></span>
<span class="line">              <span class="token operator">-></span><span class="token function">update</span><span class="token punctuation">(</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'votes'</span> <span class="token operator">=></span> <span class="token number">1</span><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h4 id="更新或插入" tabindex="-1"><a class="header-anchor" href="#更新或插入"><span>更新或插入</span></a></h4>
<p>有时你可能希望更新数据库中的记录，但如果指定记录不存在的时候则创建它。在这种情况下，可以使用 <code v-pre>updateOrInsert</code> 方法。<code v-pre>updateOrInsert</code> 方法接受两个参数：一个用于查找记录的条件数组，以及一个包含要更改记录的键值对数组。</p>
<p><code v-pre>updateOrInsert</code> 方法将尝试使用第一个参数的列名和值来定位匹配的数据库记录。如果记录存在，则使用第二个参数更新其值。如果找不到指定记录，则会合并两个参数的属性来创建一条记录并将其插入：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span></span>
<span class="line">    <span class="token operator">-></span><span class="token function">updateOrInsert</span><span class="token punctuation">(</span></span>
<span class="line">        <span class="token punctuation">[</span><span class="token string single-quoted-string">'email'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'john@example.com'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'name'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'John'</span><span class="token punctuation">]</span><span class="token punctuation">,</span></span>
<span class="line">        <span class="token punctuation">[</span><span class="token string single-quoted-string">'votes'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'2'</span><span class="token punctuation">]</span></span>
<span class="line">    <span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h3 id="更新-json-字段" tabindex="-1"><a class="header-anchor" href="#更新-json-字段"><span>更新 JSON 字段</span></a></h3>
<p>当更新一个 JSON 列的收，你可以使用 <code v-pre>-&gt;</code> 语法来更新 JSON 对象中恰当的键。此操作需要 MySQL 5.7+ 和 PostgreSQL 9.5+ 的数据库：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$affected</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span></span>
<span class="line">              <span class="token operator">-></span><span class="token function">where</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'id'</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">)</span></span>
<span class="line">              <span class="token operator">-></span><span class="token function">update</span><span class="token punctuation">(</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'options->enabled'</span> <span class="token operator">=></span> <span class="token constant boolean">true</span><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h3 id="自增与自减" tabindex="-1"><a class="header-anchor" href="#自增与自减"><span>自增与自减</span></a></h3>
<p>查询构造器还提供了方便的方法来增加或减少给定列的值。这两种方法都至少接受一个参数：要修改的列。可以提供第二个参数来指定列应该增加或减少的数量：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">increment</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'votes'</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">increment</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'votes'</span><span class="token punctuation">,</span> <span class="token number">5</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">decrement</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'votes'</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">decrement</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'votes'</span><span class="token punctuation">,</span> <span class="token number">5</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>你还可以在操作期间指定要更新的其他列：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">increment</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'votes'</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">,</span> <span class="token punctuation">[</span><span class="token string single-quoted-string">'name'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'John'</span><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div></div></div><p>此外，你可以使用 <code v-pre>incrementEach</code> 和 <code v-pre>decrementEach</code> 方法同时增加或减少多个列:</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">incrementEach</span><span class="token punctuation">(</span><span class="token punctuation">[</span></span>
<span class="line">    <span class="token string single-quoted-string">'votes'</span> <span class="token operator">=></span> <span class="token number">5</span><span class="token punctuation">,</span></span>
<span class="line">    <span class="token string single-quoted-string">'balance'</span> <span class="token operator">=></span> <span class="token number">100</span><span class="token punctuation">,</span></span>
<span class="line"><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h2 id="删除语句" tabindex="-1"><a class="header-anchor" href="#删除语句"><span>删除语句</span></a></h2>
<p>查询构建器的 <code v-pre>delete</code> 方法可用于从表中删除记录。 <code v-pre>delete</code> 方法返回受影响的行数。你可以通过在调用 <code v-pre>delete</code> 方法之前添加 <code v-pre>where</code> 子句来限制 <code v-pre>delete</code> 语句：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$deleted</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">delete</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token variable">$deleted</span> <span class="token operator">=</span> <span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">where</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'votes'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'>'</span><span class="token punctuation">,</span> <span class="token number">100</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">delete</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>如果你希望截断整个表，这将从表中删除所有记录并将自动递增 ID 重置为零，你可以使用 <code v-pre>truncate</code> 方法：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">truncate</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div></div></div><h4 id="截断表-postgresql" tabindex="-1"><a class="header-anchor" href="#截断表-postgresql"><span>截断表 &amp; PostgreSQL</span></a></h4>
<p>截断 PostgreSQL 数据库时，将应用 <code v-pre>CASCADE</code> 行为。这意味着其他表中所有与外键相关的记录也将被删除。</p>
<h2 id="悲观锁" tabindex="-1"><a class="header-anchor" href="#悲观锁"><span>悲观锁</span></a></h2>
<p>查询构建器还包括一些函数，可帮助你在执行 <code v-pre>select</code> 语句时实现「悲观锁」。 要使用「共享锁」执行语句，你可以调用 <code v-pre>sharedLock</code> 方法。共享锁可防止选定的行被修改，直到你的事务被提交：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span></span>
<span class="line">        <span class="token operator">-></span><span class="token function">where</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'votes'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'>'</span><span class="token punctuation">,</span> <span class="token number">100</span><span class="token punctuation">)</span></span>
<span class="line">        <span class="token operator">-></span><span class="token function">sharedLock</span><span class="token punctuation">(</span><span class="token punctuation">)</span></span>
<span class="line">        <span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>或者，你可以使用 <code v-pre>lockForUpdate</code> 方法。「update」锁可防止所选记录被修改或被另一个共享锁选中：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span></span>
<span class="line">        <span class="token operator">-></span><span class="token function">where</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'votes'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'>'</span><span class="token punctuation">,</span> <span class="token number">100</span><span class="token punctuation">)</span></span>
<span class="line">        <span class="token operator">-></span><span class="token function">lockForUpdate</span><span class="token punctuation">(</span><span class="token punctuation">)</span></span>
<span class="line">        <span class="token operator">-></span><span class="token function">get</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h2 id="调试" tabindex="-1"><a class="header-anchor" href="#调试"><span>调试</span></a></h2>
<p>你可以在构建查询时使用 <code v-pre>dd</code> 和 <code v-pre>dump</code> 方法来转储当前查询绑定和 SQL。 <code v-pre>dd</code> 方法将显示调试信息，然后停止执行请求。 <code v-pre>dump</code> 方法将显示调试信息，但允许请求继续执行：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">where</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'votes'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'>'</span><span class="token punctuation">,</span> <span class="token number">100</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">dd</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token class-name static-context">DB</span><span class="token operator">::</span><span class="token function">table</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'users'</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">where</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'votes'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'>'</span><span class="token punctuation">,</span> <span class="token number">100</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">dump</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><blockquote>
<p>本译文仅用于学习和交流目的，转载请务必注明文章译者、出处、和本文链接<br>
我们的翻译工作遵照 <a href="https://learnku.com/docs/guide/cc4.0/6589" target="_blank" rel="noopener noreferrer">CC 协议</a>，如果我们的工作有侵犯到您的权益，请及时联系我们。</p>
</blockquote>
<hr>
<blockquote>
<p>原文地址：<a href="https://learnku.com/docs/laravel/10.x/queries/14883" target="_blank" rel="noopener noreferrer">https://learnku.com/docs/laravel/10.x/qu...</a></p>
<p>译文地址：<a href="https://learnku.com/docs/laravel/10.x/queries/14883" target="_blank" rel="noopener noreferrer">https://learnku.com/docs/laravel/10.x/qu...</a></p>
</blockquote>
</div></template>


